IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Patient]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[Patient]
GO

CREATE TABLE [dbo].[Patient](
	[PID] [bigint] IDENTITY(1,1) NOT NULL,
	[FName] [varchar](25) NOT NULL,
	[MName] [varchar](25) NULL,
	[LName] [varchar](25) NULL,
	[FatherFName] [varchar](25) NULL,
	[FatherMName] [varchar](25) NULL,
	[FatherLName] [varchar](25) NULL,
	[Address1] [varchar](50) NOT NULL,
	[Address2] [varchar](50) NULL,
	[City] [varchar](25) NOT NULL,
	[State] [varchar](25) NULL,
	[ZipCode] [varchar](20) NULL,
	[HomePhone] [varchar](25) NULL,
	[MobilePhone] [varchar](25) NULL,
	[OfficePhone] [varchar](25) NULL,
	[DOB] [datetime] NOT NULL,
	[Sex] [varchar](1) NOT NULL
) ON [PRIMARY]

GRANT SELECT,INSERT,DELETE,UPDATE ON dbo.Patient TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_GetPatient') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_GetPatient
GO




/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/02/2008
'SP Name    : dbo.SP_GetPatient
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_GetPatient]
@vchPID VARCHAR(50)
AS
   SELECT
       PID,
       FName,
       MName,
       LName,
       FatherFName,
       FatherMName,
       FatherLName,
       Address1,
       Address2,
       City,
       State,
       ZipCode,
       MobilePhone,
       HomePhone,
       OfficePhone,
       DOB,
       Sex
   FROM Patient
   WHERE PID=@vchPID


GO

GRANT EXEC ON dbo.SP_GetPatient TO hms_usr
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'dbo.SP_SetPatient') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
   DROP PROCEDURE dbo.SP_SetPatient
GO


/*----------------------------------------------------------------------
'Created By : Preeti
'Created On : 10/02/2008
'SP Name    : dbo.SP_SetPatient
'Used In    : 
'------------------------------------------------------------------------
'Modification Log  
'------------------------------------------------------------------------
'Created By        Modified By         Remarks                             
'-----------       ------------        ----------------------------------
'                                                                          
'----------------------------------------------------------------------*/

CREATE PROCEDURE [dbo].[SP_SetPatient]

@vchPID    bigint,
@vchFName    Varchar(25),
@vchMName    Varchar(25),
@vchLName    Varchar(25),
@vchFatherFName    Varchar(25),
@vchFatherMName    Varchar(25),
@vchFatherLName    Varchar(25),
@vchMobilePhone    Varchar(25),
@vchHomePhone    Varchar(25),
@vchOfficePhone    Varchar(25),
@vchAddress1    Varchar(50),
@vchAddress2    Varchar(50),
@vchCity    Varchar(25),
@vchState    Varchar(25),
@vchZipCode    Varchar(20),
@vchDOB    Varchar(50),
@vchSex    Varchar(1),
@vchConsultant Varchar(50),
@vchPatientType Varchar(1),
@vchRegistrationFees smallmoney,
@vchConsultationFees smallmoney,
@vchInitialDeposit money,
@vchPackage Varchar(50),
@vchOutPID bigint OUTPUT,
@vchOutCRN bigint OUTPUT
                    
AS
DECLARE  @iRetValue integer, 
	     @iConsultantID integer,
		 @iPackageID integer
BEGIN TRANSACTION
   IF NOT EXISTS(SELECT     PID
   FROM Patient
   WHERE   (PID = @vchPID)
   )

   BEGIN
   INSERT INTO Patient(
       FName,
	   MName,
       LName,
	   FatherFName,
	   FatherMName,
	   FatherLName,
       Address1,
       Address2,
       City,
       State,
       ZipCode,
	   MobilePhone,
       HomePhone,
       OfficePhone,
       DOB,
	   Sex
  )
   VALUES (
       @vchFName,
	   @vchMName,
       @vchLName,
	   @vchFatherFName,
	   @vchFatherMName,
       @vchFatherLName,
       @vchAddress1,
       @vchAddress2,
       @vchCity,
       @vchState,
       @vchZipCode,
       @vchMobilePhone,
       @vchHomePhone,
       @vchOfficePhone,
       @vchDOB,
	   @vchSex 
   )
	SELECT @vchPID = @@Identity
   END
ELSE  IF EXISTS(SELECT     PID
   FROM Patient
   WHERE   (PID = @vchPID)
   )

   BEGIN
   UPDATE Patient
   SET
       FName = @vchFName,
	   MName = @vchMName,
       LName = @vchLName,
	   FatherFName = @vchFatherFName,
	   FatherMName = @vchFatherMName,
       FatherLName = @vchFatherLName,
       Address1 = @vchAddress1,
       Address2 = @vchAddress2,
       City = @vchCity,
       State = @vchState,
       ZipCode = @vchZipCode,
       MobilePhone = @vchMobilePhone,
       HomePhone = @vchHomePhone,
       OfficePhone = @vchOfficePhone,
       DOB = @vchDOB,
	   Sex = @vchSex 
   WHERE (PID = @vchPID)
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg1 varchar(800)
       SET @SPErrMsg1 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in Patient table.'
       RAISERROR (@SPErrMsg1,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
	   SET @vchOutPID = @vchPID
   END

   SELECT @vchOutCRN = CRNumber FROM GlobalData
   IF @vchConsultant IS NOT NULL
       SELECT @iConsultantID = ID FROM Consultant WHERE [Name]=@vchConsultant
   IF @vchPackage IS NOT NULL
	   SELECT @iPackageID = ID FROM Package WHERE [Name]=@vchPackage

   BEGIN
   INSERT INTO OpenCases(
       CRNumber,
       PID,
	   Consultant,
       PatientType,
	   RegistrationFees,
	   ConsultationFees,
	   InitialDeposit,
	   Package,
	   RegistrationTime
  )
   VALUES (
       @vchOutCRN,
       @vchPID,
	   @biConsultantID,
       @vchPatientType,
	   @vchRegistrationFees,
	   @vchConsultationFees ,
	   @vchInitialDeposit,
	   @biPackageID,
	   getdate()
   )
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg2 varchar(800)
       SET @SPErrMsg2 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in OpenCases table.'
       RAISERROR (@SPErrMsg2,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

   BEGIN
   UPDATE GlobalData
   SET
      CRNumber = @vchOutCRN + 1
   END

IF (@@ERROR <> 0)
   BEGIN
	   ROLLBACK
       declare @SPErrMsg3 varchar(800)
       SET @SPErrMsg3 = 'Error: Error in Stored procedure dbo.SP_SetPatient for PID : ' + CAST (@vchPID as varchar) + ' in OpenCases table.'
       RAISERROR (@SPErrMsg3,16,1)
       Select @iRetValue = -1
	   RETURN @iRetValue
   END
ELSE
   BEGIN
       Select @iRetValue = 0
   END

COMMIT
   
   RETURN @iRetValue

GO

GRANT EXEC ON dbo.SP_SetPatient TO hms_usr

GO